﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidades;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAO
{
    public static class HorarioDAO
    {
        public static void insert(Horario h)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "insert into Horario(horario_inicio,horario_Fin,dia_Semana) values (@horI,@horF,@dia)";
                cmd.Parameters.AddWithValue("@horI", h.Horario_Inicio);
                cmd.Parameters.AddWithValue("@horF", h.Horario_Fin);
                cmd.Parameters.AddWithValue("@dia", h.Dia_Semana);          
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al insertar el horario:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }

        }
        public static void update(Horario h)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "update Horario set horario_inicio=@horI,horario_Fin=@horF,dia_Semana=@dia where id_Horario=@Id";
                cmd.Parameters.AddWithValue("@Id", h.Id_Horario);
                cmd.Parameters.AddWithValue("@horI", h.Horario_Inicio);
                cmd.Parameters.AddWithValue("@horF", h.Horario_Fin);
                cmd.Parameters.AddWithValue("@dia", h.Dia_Semana);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al actualizar el horario:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }

        }
        public static void darBajaHorario(int idHorario)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                cn.Open();
                cmd.Connection = cn;
                cmd.CommandText = "delete from Horario where id_Horario=@id";
                cmd.Parameters.Add(new SqlParameter("@id", idHorario));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al eliminar el horario:" + ex.Message);

            }
            finally
            {
                cn.Close();

            }
        }
        public static Horario buscarPorId(int idHorario)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            Horario h=new Horario();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Horario,convert(char(5),horario_Inicio,114) as horario_Inicio,convert(char(5),horario_Fin,114) as horario_Fin,dia_Semana from Horario where id_Horario=@Id";
                cmd.Parameters.AddWithValue("@Id", idHorario);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    h.Id_Horario = int.Parse(dr["id_Horario"].ToString());
                    h.Horario_Inicio = DateTime.Parse(dr["horario_Inicio"].ToString());
                    h.Horario_Fin = DateTime.Parse(dr["horario_Fin"].ToString());
                    h.Dia_Semana = dr["dia_Semana"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("error al buscar el horario:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }

            return h;
        }
        public static DataTable cargarGrilla(string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Horario,convert(char(5),horario_Inicio,114) as horario_Inicio,convert(char(5),horario_Fin,114) as horario_Fin,dia_Semana from Horario";
                if (orden == "")
                    cmd.CommandText += " order by dia_Semana";
                else
                {                
                    cmd.CommandText += " order by " + orden;                  
                }
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;

        }
        public static DataTable cargarGrillaByFiltro(string diaSemana,string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                string sql = "select id_Horario,convert(char(5),horario_Inicio,114) as horario_Inicio,convert(char(5),horario_Fin,114) as horario_Fin,dia_Semana from Horario where 1=1";

                if (!string.IsNullOrEmpty(diaSemana))
                {
                    sql += " and dia_Semana like @dia";
                    cmd.Parameters.AddWithValue("@dia", diaSemana + "%");
                }               
                cmd.CommandText = sql;
                if (orden == "")
                    cmd.CommandText += " order by dia_Semana";
                else
                {
                    cmd.CommandText += " order by " + orden;
                }
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar grilla por filtros:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }
        public static DataTable cargarComboHoraInicio(string diaSemana)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Horario,convert(char(5),horario_Inicio,114) as horario_Inicio ,dia_Semana from Horario where dia_Semana=@Dia";
                cmd.Parameters.AddWithValue("@Dia", diaSemana);
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;

        }
        public static string obtenerHoraFin(int idHorario)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            String dt = "";
            try
            {
                cn.Open();
                cmd.CommandText = "select convert(char(5),horario_Fin,114) as horario_Fin from Horario where id_Horario=@Id";
                cmd.Parameters.AddWithValue("@Id", idHorario);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    dt = dr["horario_Fin"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al mostrar horario fin" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;

        }
        public static DateTime obtenerHoraInicio(int id)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DateTime dt = new DateTime();
            try
            {
                cn.Open();
                cmd.CommandText = "select horario_Inicio from Horario where id_Horario=@Id";
                cmd.Parameters.AddWithValue("@Id", id);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    dt = DateTime.Parse(dr["horario_Inicio"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al buscar el horario_Inicio:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;

        }
    }
}
